<?php
function seitcm_add_single_score($stu,$classid,$points,$skill,$type){
	global $wpdb;
	$time = seitcm_get_current_sql_datetime();
	$sql=$wpdb->prepare('INSERT INTO '. SEITCM_TABLE_SCORE . ' (studentid,classid,points,skill,type,time) VALUES (%d,%d,%d,%d,%d,%s)',$stu,$classid,$points,$skill,$type,$time);
	if(!$wpdb->query($sql)){
		return false;
	}
	return true;
}
function seitcm_add_score($stuscore,$classid,$skill,$type){
	global $wpdb;
	$wpdb->query('start transaction');
	foreach ($stuscore as $stu){
		$time = seitcm_get_current_sql_datetime();
		$sql=$wpdb->prepare('INSERT INTO '. SEITCM_TABLE_SCORE . ' (studentid,classid,points,skill,type,time) VALUES (%d,%d,%d,%d,%d,%s)',$stu['id'],$classid,$stu['score'],$skill,$type,$time);
		if(!$wpdb->query($sql)){
			$wpdb->query('rollback');
			return false;
		}
	}
	$wpdb->query('commit');
	return true;
}
/**
 * 
 * @param unknown $stuscore
 * @param unknown $classid
 * @return boolean
 */
function seitcm_add_score_exam($stuscore,$classid){
	global $wpdb;
	$wpdb->query('start transaction');
	foreach ($stuscore as $stu){
		$time = seitcm_get_current_sql_datetime();
		if(!($stu['score_r'] == '-1' && $stu['score_w'] == '-1' && $stu['score_l'] == '-1' && $stu['score_s'] == '-1'))
		{
		$sql=$wpdb->prepare('INSERT INTO '. SEITCM_TABLE_SCORE . ' (studentid,classid,points,skill,type,time) VALUES (%d,%d,%d,%d,%d,%s)',$stu['id'],$classid,$stu['score_r'],1,1,$time);
		if(!$wpdb->query($sql)){
			$wpdb->query('rollback');
			return false;
		}
		$sql=$wpdb->prepare('INSERT INTO '. SEITCM_TABLE_SCORE . ' (studentid,classid,points,skill,type,time) VALUES (%d,%d,%d,%d,%d,%s)',$stu['id'],$classid,$stu['score_w'],2,1,$time);
		if(!$wpdb->query($sql)){
			$wpdb->query('rollback');
			return false;
		}
		$sql=$wpdb->prepare('INSERT INTO '. SEITCM_TABLE_SCORE . ' (studentid,classid,points,skill,type,time) VALUES (%d,%d,%d,%d,%d,%s)',$stu['id'],$classid,$stu['score_l'],3,1,$time);
		if(!$wpdb->query($sql)){
			$wpdb->query('rollback');
			return false;
		}
		$sql=$wpdb->prepare('INSERT INTO '. SEITCM_TABLE_SCORE . ' (studentid,classid,points,skill,type,time) VALUES (%d,%d,%d,%d,%d,%s)',$stu['id'],$classid,$stu['score_s'],4,1,$time);
		if(!$wpdb->query($sql)){
			$wpdb->query('rollback');
			return false;
		}
		}
	}
	$wpdb->query('commit');
	return true;
}
function seitcm_get_list_score_for_class($classid)
{
	global $wpdb;
	return $wpdb->get_results($wpdb->prepare('SELECT * FROM ' . SEITCM_TABLE_SCORE . ' WHERE `classid` = %d',$classid));
}
function seitcm_get_list_periodicreviews($classid,$studentid)
{
	global $wpdb;
	$sql=$wpdb->prepare('SELECT `points`,`skill`,`time` FROM ' . SEITCM_TABLE_SCORE . ' WHERE classid = %d AND studentid = %d AND `type` = 0 ORDER BY `time`',$classid,$studentid);
	return $wpdb->get_results($sql);
}
function seitcm_get_list_examinationscores($classid,$studentid)
{
	global $wpdb;
	$sql=$wpdb->prepare('SELECT 
							SUM(CASE WHEN `skill` = 1 THEN `points` ELSE 0 END) as skill1,
							SUM(CASE WHEN `skill` = 2 THEN `points` ELSE 0 END) as skill2,
							SUM(CASE WHEN `skill` = 3 THEN `points` ELSE 0 END) as skill3,
							SUM(CASE WHEN `skill` = 4 THEN `points` ELSE 0 END) as skill4,
							`time` FROM ' . SEITCM_TABLE_SCORE . ' WHERE classid = %d 
								AND studentid = %d AND `type` = 1 GROUP BY `time` ORDER BY `time`',$classid,$studentid);
	return $wpdb->get_results($sql);
}
function seitcm_get_average_score_for_student_class($classid,$studentid)
{
	global $wpdb;
	$sql=$wpdb->prepare('SELECT AVG(result) FROM (SELECT SUM(`points`)/COUNT(`points`) as result FROM ' . SEITCM_TABLE_SCORE . ' WHERE classid = %d
								AND studentid = %d AND `type` = 1 GROUP BY `time` ORDER BY `time`) as A',$classid,$studentid);
	$avg_exam = round($wpdb->get_var($sql),2);
	
	$sql=$wpdb->prepare('SELECT AVG(`points`) FROM ' . SEITCM_TABLE_SCORE . ' WHERE classid = %d
								AND studentid = %d AND `type` = 0 ORDER BY `time`',$classid,$studentid);
	$avg_perio = round($wpdb->get_var($sql),2);
	global $seitcm_coefficient;
	return round((($avg_perio*$seitcm_coefficient['periodic'])/10 + ($avg_exam*$seitcm_coefficient['exam'])/10),2);
}
?>